﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DCTLib.Common;
using System.Data;

namespace DCTAccess.Business.Search
{
    public class SearchFilter
    {
        private int _locationID = -1;        
        private string _title = string.Empty;
        private string _services = string.Empty;
        private string _location = string.Empty;
        private int _price = -1;
        private int _priceTo = -1;
        private int _viewNum = -1;
        private string _datePost = string.Empty;
        private string _imagesPost = string.Empty;
        private string _need = string.Empty;
        private string _locationCity = string.Empty;

        #region Property       
        
        public int PriceTo
        {
            get { return _priceTo; }
            set { _priceTo = value; }
        }

        public int Price
        {
            get { return _price; }
            set { _price = value; }
        }

        public int LocationID
        {
            get { return _locationID; }
            set { _locationID = value; }
        }
        public string Title
        {
            get { return _title; }
            set { _title = value; }
        }        

        public string Services
        {
            get { return _services; }
            set { _services = value; }
        }        

        public string Location
        {
            get { return _location; }
            set { _location = value; }
        }                   

        public int ViewNum
        {
            get { return _viewNum; }
            set { _viewNum = value; }
        }        

        public string DatePost
        {
            get { return _datePost; }
            set { _datePost = value; }
        }        

        public string ImagesPost
        {
            get { return _imagesPost; }
            set { _imagesPost = value; }
        }

        

        public string Need
        {
            get { return _need; }
            set { _need = value; }
        }

        

        public string LocationCity
        {
            get { return _locationCity; }
            set { _locationCity = value; }
        }
        #endregion

        private string SelectAllSQL
        {
            get
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("select l.LocationID,l.LocationName, l.ParentID, p.LocationName as ParentName, ");
                sql.Append(" pr.*,fp.FunctionPageName,n.NeedsName, u.UserName from Location l ");
                sql.Append(" left join Location p on l.ParentID = p.LocationID ");
                sql.Append(" inner join Product pr ON pr.LocationID = l.LocationID ");
                sql.Append(" inner join FunctionPage fp on pr.CategoryPageID =  fp.FunctionPageID");
                sql.Append(" inner join Needs n on n.NeedsID = pr.NeedsID ");
                sql.Append(" inner join Users u on u.UserID = pr.CreateBy");
                sql.Append(" Where 1=1 ");

                return sql.ToString();
            }
        } 

        public DataTable GetSearchResult()
        {
            SQLHelper sqlHelper = new SQLHelper();
            sqlHelper.SwitchCommandType(CommandType.Text);

            StringBuilder sql = new StringBuilder(SelectAllSQL);

            if (_locationID != -1)
            {
                sql.Append("AND " + IntegerCompareSQLBuilder.GetSQL("LocationID", IntegerCompareTypes.Equals, "@LocationID"));
                sqlHelper.AddParameter("@LocationID", _locationID);
            }

            if (_title.Trim().Length > 0)
            {
                sql.Append("AND " + StringCompareSQLBuilder.GetSQL("ProductName", StringCompareTypes.ContainsN, "@ProductName"));
                sqlHelper.AddParameter("@ProductName", _title);
            }

            if (!_services.Equals("-1"))
            {
                sql.Append("AND " + StringCompareSQLBuilder.GetSQL("FunctionPageName", StringCompareTypes.ContainsN, "@FunctionPageName"));
                sqlHelper.AddParameter("@FunctionPageName", _services);
            }

            if (_price != -1)
            {
                sql.Append("AND " + StringCompareSQLBuilder.GetSQL("Price", StringCompareTypes.EqualThan, "@Price"));
                sqlHelper.AddParameter("@Price", _price);
            }

            if (_priceTo != -1)
            {
                sql.Append("AND " + StringCompareSQLBuilder.GetSQL("Price", StringCompareTypes.NotEqualThan, "@PriceTo"));
                sqlHelper.AddParameter("@Price", _priceTo);
            }

            if (_location.Trim().Length > 0)
            {
                sql.Append("AND " + StringCompareSQLBuilder.GetSQL("LocationName", StringCompareTypes.ContainsN, "@LocationName"));
                sqlHelper.AddParameter("@LocationName",_location);
            }

            if (_locationCity.Trim().Length > 0)
            {
                sql.Append("AND " + StringCompareSQLBuilder.GetSQL("LocationName", StringCompareTypes.ContainsN, "@LocationName"));
                sqlHelper.AddParameter("@LocationName", _locationCity);
            }

            if (!_need.Equals("-1"))
            {
                sql.Append("AND " + StringCompareSQLBuilder.GetSQL("NeedsName", StringCompareTypes.ContainsN, "@NeedsName"));
                sqlHelper.AddParameter("@NeedsName", _need);
            }

            return sqlHelper.GetDataTable(sql.ToString() + " ORDER BY pr.CreateDate DESC ");
        }
    }
}
